﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Data;


namespace PhotoViewerLib
{
    // Class with static methods to access and perform requests on the sqlite database
    class Database
    {
        private static string dbPath = "..\\..\\..\\..\\PhotoViewer.s3db";
        

        // Returns the connection to the db
        private static SQLiteConnection getConn()
        {
            SQLiteConnectionStringBuilder sqlcsb = new SQLiteConnectionStringBuilder();
            sqlcsb.DataSource = dbPath;
            sqlcsb.FailIfMissing = true;

            string cns = sqlcsb.ToString();
            return new SQLiteConnection(cns);
        }


        // Executes a request that returns multiple results
        private static DataTable executeMultiResultSelect(string select)
        {
            DataTable dt = new DataTable();
            try
            {
                SQLiteConnection sql_con = Database.getConn();
                if (sql_con.State != ConnectionState.Open)
                    sql_con.Open();
                SQLiteCommand sql_cmd = sql_con.CreateCommand();
                sql_cmd.CommandText = select;

                SQLiteDataReader reader = sql_cmd.ExecuteReader();
                dt.Load(reader);
                reader.Close();
                sql_con.Close();
            }
            catch (Exception e){ }
            return dt;

        }

        // Executes a request with no other result than the number of rows affected
        // such as insert, update or delete
        private static Boolean executeNonQuery(string query)
        {
            int ret = 0;
            try
            {
                SQLiteConnection sql_con = Database.getConn();
                if (sql_con.State != ConnectionState.Open)
                    sql_con.Open();
                SQLiteCommand sql_cmd = sql_con.CreateCommand();
                sql_cmd.CommandText = query;

                ret = sql_cmd.ExecuteNonQuery();
                Console.WriteLine(ret + " lignes affectées");
                sql_con.Close();
            }
            catch (Exception e) {
                Console.WriteLine(e.ToString());
            }
            return (ret > 0);

        }

        // Execute a request that affects only one row
        private static String executeScalar(string query)
        {
            SQLiteConnection sql_con = Database.getConn();
            if (sql_con.State != ConnectionState.Open)
                sql_con.Open();
            SQLiteCommand sql_cmd = sql_con.CreateCommand();
            sql_cmd.CommandText = query;
            sql_con.Close();
            return ((sql_cmd.ExecuteScalar()).ToString());
        }

        // Returns the list of all photos
        public static List<Photo> getListPhotos()
        {
            List<Photo> ret = new List<Photo>();
            DataTable res = Database.executeMultiResultSelect("select * from photos");
            foreach (DataRow dr in res.Select())
            {
                ret.Add(new Photo(dr));
            }
            return ret;
        }

        // Returns the photos for a given category
        public static List<Photo> getPhotosForCategory(Category c)
        {
            List<Photo> ret = new List<Photo>();
            DataTable res = Database.executeMultiResultSelect("select * from photos where categId="+c.uid);
            foreach (DataRow dr in res.Select())
            {
                ret.Add(new Photo(dr));
            }
            return ret;
        }

        // Gets the tags for a given photo
        public static List<Tag> getTagsForPhoto(Photo p)
        {
            List<Tag> ret = new List<Tag>();
            DataTable res = Database.executeMultiResultSelect("select * from Tag where photoId=" + p.uid);
            foreach (DataRow dr in res.Select())
            {
                ret.Add(new Tag(dr));
            }
            return ret;
        }

        // Returns the list of categories
        public static List<Category> getListCategory()
        {
            List<Category> ret = new List<Category>();
            DataTable res = Database.executeMultiResultSelect("select * from Categories");
            foreach (DataRow dr in res.Select())
            {
                ret.Add(new Category(dr));
            }
            return ret;
        }

        // Adds a photo
        public static void addPhoto(int categ_id, string filePath, int rating)
        {
            if (!Database.executeNonQuery("insert into Photos(categId, filePath, rating) values ('" + categ_id + "', '" + filePath + "', " + rating + ");"))
            {
                Console.WriteLine("Error when inserting a new Photo occured");
            }
        }

        // Adds a tag
        public static void addTag(int photo_id, string tag)
        {
            if (!Database.executeNonQuery("insert into Tag(photoId, tag) values ('" + photo_id + "', '" + tag + "');"))
            {
                Console.WriteLine("Error when inserting a new Tag occured");
            }
        }

        // Adds a category
        public static void addCategorie(string name)
        {
            if (!Database.executeNonQuery("insert into Categories(name) values ('" + name + "');"))
            {
                Console.WriteLine("Error when inserting a new Categorie occured");
            }
        }

        // Updates a photo
        public static Boolean editPhoto(int photo_id, int categ_id, string filePath, int rating)
        {
            if (!Database.executeNonQuery("update Photos set categId='" + categ_id + "', filePath='" + filePath + "', rating='" + rating + "' where id=" + photo_id + ";"))
            {
                Console.WriteLine("Error when updating photo " + photo_id);
                return false;
            }
            return true;
        }

        // Updates a category
        public static Boolean editCategorie(int categ_id, string newCategName)
        {
            if (!Database.executeNonQuery("update Categories set name='" + newCategName + "' where uId=" + categ_id + ";"))
            {
                Console.WriteLine("Error when updating category " + categ_id);
                return false;
            }
            return true;
        }

        // Deletes a photo
        public static void delPhoto(int photo_id)
        {
            if (!Database.executeNonQuery("delete from Photos where id='" + photo_id + "';"))
            {
                Console.WriteLine("Error when deleting photo " + photo_id);
            }
        }

        // Deletes all photos
        public static void delAllPhoto()
        {
            if (!Database.executeNonQuery("delete from Photos;"))
            {
                Console.WriteLine("Error when deleting all photos");
            }
        }

        // Deletes a cateogory
        public static void delCateg(int categ_id)
        {
            if (!Database.executeNonQuery("delete from Categories where uId='" + categ_id + "';"))
            {
                Console.WriteLine("Error when updating categorie " + categ_id);
            }
        }

        // Deletes all categories
        public static void delAllCateg()
        {
            if (!Database.executeNonQuery("delete from Categories;"))
            {
                Console.WriteLine("Error when deleting all categories");
            }
        }

        // Returns the current path for the database file
        internal static string getDbFilePath()
        {
            return dbPath;
        }


        // Sets the new database path
        internal static void setDbFilePath(string dbFilePath)
        {
            dbPath = dbFilePath;
        }

    }
}
